<?php

namespace QingPHP\Lib;

/**
 * php操作mysql的工具类
 */
class MysqlLib
{
    private $_db = null;//数据库连接句柄
    private $_table = null;//表名
    private $_where = null;//where条件
    private $_order = null;//order排序
    private $_limit = null;//limit限定查询
    private $_group = null;//group分组


    /**
     * 构造函数，连接数据库
     */
    public function __construct($config = [])
    {
        $config = empty($config) ? getMysql() : $config;

        $link = $this->_db;

        if (!$link) {
            $db = mysqli_connect($config['hostname'], $config['username'], $config['password'], $config['database'], $config['hostport']);
            if (!$db) {
                $this->ShowException("错误信息" . mysqli_connect_error());
            }
            mysqli_query($db, "set names utf8");

            $this->_db = $db;
        }

    }

    /**
     * 获取所有数据
     *
     * @param <type> $table The table
     *
     * @return boolean  All.
     */
    public function getAll($table = null)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }
        $sql = "SELECT * FROM {$table}";
        $data = mysqli_fetch_all($this->execute($sql));
        return $data;
    }

    public function table($table)
    {
        $this->_table = "`$table`";
        return $this;
    }

    /**
     * 实现查询操作
     *
     * @param string $fields The fields
     *
     * @return boolean  ( description_of_the_return_value )
     */
    public function select($fields = "*")
    {
        $fieldsStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }
        if (is_array($fields)) {
            $fieldsStr = implode(',', $fields);
        } elseif (is_string($fields) && !empty($fields)) {
            $fieldsStr = $fields;
        }
        $sql = "SELECT {$fieldsStr} FROM {$this->_table} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
//        echo $sql.PHP_EOL;
//        file_put_contents("./tmp/db.sql", $sql . PHP_EOL, FILE_APPEND);

        $res = $this->execute($sql);
        $result = [];
        while ($row = mysqli_fetch_assoc($res)) {
            $result[] = $row;
        }

        $this->_where = null;
        return $result;
    }

    public function count()
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        $sql = "SELECT count(*) as num FROM {$this->_table} {$this->_where} {$this->_group} {$this->_order} {$this->_limit}";
//        echo $sql.PHP_EOL;
//        file_put_contents("./tmp/db.sql", $sql . PHP_EOL, FILE_APPEND);

        $res = $this->execute($sql);
        $result = [];
        while ($row = mysqli_fetch_assoc($res)) {
            $result[] = $row;
        }

        $this->_where = null;
        return $result;
    }

    public function query(string $sql)
    {

        $res = $this->execute($sql);
        $result = [];
        while ($row = mysqli_fetch_assoc($res)) {
            $result[] = $row;
        }

        $this->_where = null;
        return $result;
    }

    public function getFields($tablesName)
    {
        $sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name = '{$tablesName}' ";


        $result = $this->query($sql);

        $result = array_column($result, "COLUMN_NAME");

        return $result;
    }

    public function get($fields = "*")
    {
        return $this->limit(1)->select($fields)[0] ?? false;
    }

    /**
     * order排序
     *
     * @param string $order The order
     *
     * @return boolean  ( description_of_the_return_value )
     */
    public function order($order = '')
    {
        $orderStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }
        if (is_string($order) && !empty($order)) {
            $orderStr = "ORDER BY " . $order;
        }
        $this->_order = $orderStr;
        return $this;
    }

    /**
     * where条件
     *
     * @param string $where The where
     *
     * @return <type>  ( description_of_the_return_value )
     */
    public function where($where = '')
    {
        if (empty($where)) {
            return $this;
        }

        $whereStr = is_null($this->_where) ? 'WHERE ' : "$this->_where AND ";
        $link = $this->_db;
        if (!$link) {
            return $link;
        }
        if (is_array($where)) {
            foreach ($where as $key => $value) {
                if (is_numeric($key) && is_string($value)) {
                    $whereStr .= " $value AND ";
                } elseif (is_array($value)) {
                    if ($value[0] == 'in'  && is_array($value[1])) {
                        $idStr = implode($value[1],",");
                        $whereStr .= "`$key` in($idStr) AND ";
                    } else {
                        $whereStr .= "`$key` {$value[0]} '{$value[1]}' AND ";
                    }
                } else {
                    $whereStr .= "`" . $key . "` = '" . $value . "' AND ";
                }
            }
        } elseif (is_string($where) && !empty($where)) {
            $whereStr .= $where;
        }


        $this->_where = rtrim($whereStr, 'AND ');
        return $this;
    }

    /**
     * group分组
     *
     * @param string $group The group
     *
     * @return boolean  ( description_of_the_return_value )
     */
    public function group($group = '')
    {
        $groupStr = '';
        $link = $this->_db;
        if (!$link) {
            return false;
        }
        if (is_array($group)) {
            $groupStr = "GROUP BY " . implode(',', $group);
        } elseif (is_string($group) && !empty($group)) {
            $groupStr = "GROUP BY " . $group;
        }
        $this->_group = $groupStr;
        return $this;
    }

    /**
     * limit限定查询
     *
     * @param string $limit The limit
     *
     * @return <type>  ( description_of_the_return_value )
     */
    public function limit($limit = '')
    {
        $limitStr = '';
        $link = $this->_db;
        if (!$link) {
            return $link;
        }
        if (is_string($limit) || !empty($limit)) {
            $limitStr = "LIMIT " . $limit;
        } elseif (is_numeric($limit)) {
            $limitStr = "LIMIT " . $limit;
        }
        $this->_limit = $limitStr;
        return $this;
    }

    /**
     * 执行sql语句
     *
     * @param <type> $sql The sql
     *
     * @return boolean  ( description_of_the_return_value )
     */
    public function execute($sql = null)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }
        $res = mysqli_query($this->_db, $sql);
        if (!$res) {
            $errors = mysqli_error_list($this->_db);
            $this->ShowException("SQL语句：{$sql}  报错啦！<br/>错误号：" . $errors[0]['errno'] . "<br/>SQL错误状态：" . $errors[0]['sqlstate'] . "<br/>错误信息：" . $errors[0]['error']);
            die();
        }
        return $res;
    }

    /**
     * 插入数据
     *
     * @param <type> $data The data
     *
     * @return boolean  ( description_of_the_return_value )
     */
    public function insert($data, int $type = 1)
    {
        $insertArr = ['INSERT', 'INSERT ignore', 'REPLACE'];
        $insertStr = $insertArr[$type];

        $link = $this->_db;
        if (!$link) {
            return false;
        }
        if (is_array($data)) {
            $keys = '';
            $values = '';
            foreach ($data as $key => $value) {
                $keys .= "`" . $key . "`,";
                $values .= "'" . $value . "',";
            }
            $keys = rtrim($keys, ',');
            $values = rtrim($values, ',');
        }

        $sql = "$insertStr INTO {$this->_table} ({$keys}) VALUES({$values})";


        mysqli_query($this->_db, $sql);
        $insertId = mysqli_insert_id($this->_db);

        return $insertId;
    }

    public function batchInsert(array $dataList, $replace = false)
    {
        $link = $this->_db;
        if (!$link) {
            return false;
        }

        $keys = '';
        $values = '';

        //获取key
        foreach ($dataList[0] as $key => $value) {
            $keys .= "`" . $key . "`,";
        }
        $keys = rtrim($keys, ',');

        $sql = '';
        foreach ($dataList as $data) {
            //获取values
            $values = '';
            foreach ($data as $value) {
                $values .= "'" . $value . "',";
            }
            //删除最后一个,
            $values = rtrim($values, ',');

            $insertStr = $replace ? 'INSERT ignore' : 'INSERT';
            $sql = "$insertStr INTO {$this->_table}({$keys}) VALUES ({$values});" . PHP_EOL;
            $res = mysqli_query($this->_db, $sql);
            if (!$res) {
                $errors = mysqli_error_list($this->_db);
                $this->ShowException("报错啦！<br/>错误号：" . $errors[0]['errno'] . "<br/>SQL错误状态：" . $errors[0]['sqlstate'] . "<br/>错误信息：" . $errors[0]['error']);
            }
        }


        $insertId = mysqli_insert_id($this->_db);

        return $insertId;
    }


    /**
     * 更新数据
     *
     * @param <type> $data The data
     *
     * @return <type>  ( description_of_the_return_value )
     */
    public function update($data)
    {
        $link = $this->_db;
        if (!$link) {
            return $link;
        }
        if (is_array($data)) {
            $dataStr = '';
            foreach ($data as $key => $value) {
                $dataStr .= "`" . $key . "`='" . $value . "',";
            }
            $dataStr = rtrim($dataStr, ',');
        }
        $sql = "UPDATE {$this->_table} SET {$dataStr} {$this->_where} {$this->_order} {$this->_limit}";
        $res = $this->execute($sql);
        $this->_where = null;
        return $res;
    }

    /**
     * 删除数据
     *
     * @return <type>  ( description_of_the_return_value )
     */
    public function delete()
    {
        $link = $this->_db;
        if (!$link) {
            return $link;
        }
        $sql = "DELETE FROM {$this->_table} {$this->_where}";
        $res = $this->execute($sql);

        $this->_where = null;
        return $res;
    }

    /**
     * 异常信息输出
     *
     * @param <type> $var The variable
     */
    private function ShowException($var)
    {
        if (is_bool($var)) {
            var_dump($var);
        } else {
            if (is_null($var)) {
                var_dump(null);
            } else {
                echo "<pre style='position:relative;z-index:1000;padding:10px;border-radius:5px;background:#f5f5f5;border:1px solid #aaa;font-size:14px;line-height:18px;opacity:0.9;'>" . print_r(
                        $var,
                        true
                    ) . "</pre>";
            }
        }
    }
}
